// 定义数据库实例名称
let dbName = "cloud"
 
export const openDb = () => {
	//如果数据库存在则打开，不存在则创建。
	return new Promise((resolve, reject) => {
		plus.sqlite.openDatabase({
			name: dbName, //数据库名称
			path: `_doc/${dbName}.db`, //数据库地址
			// path: `/storage/emulated/0/transfer/${dbName}.db`,
			success(e) {
				resolve(true);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}

// 查询所有数据表名
export const getTable = () => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select * FROM sqlite_master where type='table'",
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 查询表数据总条数
export const getCount = (tabName) => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select count(*) as num from " + tabName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}
 
// 查询表是否存在
export const isTable = (tabName) => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
			success(e) {
				resolve(e[0].isTable ? true : false);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

 
// 删除数据库数据
export const deleteInformationType = (tabName,setData) => {
	if (JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
			console.log(setData[item])
			setStr += (
				`${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`)
		})
		let sql = `delete from ${tabName} where ${setStr}`
		console.log(sql)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: dbName,
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

// 关闭数据库
export const closeSQL = () => {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: dbName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}

// 监听数据库是否开启
export const isOpen = () => {
	let open = plus.sqlite.isOpenDatabase({
		name: dbName,
		path: `_doc/${dbName}.db`, 
	})
	return open;
}

// 创建表
export const createTab = (tabName, data) => {
	// tabName不能用数字作为表格名的开头
	return new Promise((resolve, reject) => {
		console.log("创建数据库表",tabName)
		plus.sqlite.executeSql({
			name: dbName,
			// sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
			sql: `create table if not exists ${tabName}(${data})`,
			success(e) {
				cosole.log("创建数据表成功")
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 删除表
export const dropTab = (tabName) => {
	if(null == tabName || tabName.length == 0){
		return false;
	}
	// tabName不能用数字作为表格名的开头
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			// sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
			sql: `DROP TABLE IF EXISTS ${tabName};`,
			success(e) {
				cosole.log(`删除数据表成功`)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}
// 添加数据
export const addTabItem = (tabName,obj) => {
	if (obj) {
		let keys = Object.keys(obj)
		let keyStr = keys.toString()
		let valStr = ''
		keys.forEach((item, index) => {
			if (keys.length - 1 == index) {
				valStr += ('"' + obj[item] + '"')
			} else {
				valStr += ('"' + obj[item] + '",')
			}
		})
		let sqlStr = `insert into ${tabName}(${keyStr}) VALUES (${valStr})`
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: dbName,
				sql: sqlStr,
				success(e) {
					console.log('insert success',e)
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		})
	}
}

// 合并数据
export const mergeSql = (tabName,tabs) => {
	if (!tabs || tabs.length == 0) {
		return new Promise((resolve, reject) => {
			reject("错误")
		})
	}
	let itemValStr = ''
	tabs.forEach((item, index) => {
		let itemKey = Object.keys(item)
		let itemVal = ''
		itemKey.forEach((key, i) => {
			if (itemKey.length - 1 == i) {
				if (typeof item[key] == 'object') {
					itemVal += (`'${JSON.stringify(item[key])}'`)
				} else {
					itemVal += (`'${item[key]}'`)
				}
			} else {
				if (typeof item[key] == 'object') {
					itemVal += (`'${JSON.stringify(item[key])}',`)
				} else {
					itemVal += (`'${item[key]}',`)
				}
			}
		})
		if (tabs.length - 1 == index) {
			itemValStr += ('(' + itemVal + ')')
		} else {
			itemValStr += ('(' + itemVal + '),')
		}
	})
	let keys = Object.keys(tabs[0])
	let keyStr = keys.toString()
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			sql: `insert or ignore into ${tabName} (${keyStr}) values ${itemValStr}`,
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 获取分页数据库数据
export const getDataList = async (tabName, num, size, byName, byType) => {
	let count = 0
	let sql = ''
	let numindex = 0
	await getCount(tabName).then((resNum) => {
		count = Math.ceil(resNum[0].num / size)
	})
	if(((num - 1) * size) == 0) {
	    numindex = 0
	} else {
		numindex = ((num - 1) * size)
	}
	sql = `select * from ${tabName}`
	if(byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	sql += ` limit ${numindex},${size}`
	console.log(sql)
	if (count < num - 1) {
		return new Promise((resolve, reject) => {
			reject("无数据")
		});
	} else {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: dbName,
				// sql: "select * from userInfo limit 3 offset 3",
				sql:sql ,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}

// 修改数据 完整SQL修改
export const updateBySQL = (sql) => {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			sql: sql,
			success(e) {
				console.log(e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 修改数据 指定条件数据
export const updateSQL = (tabName, setData, setName, setVal) => {
	if (JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
			setStr += (
				`${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}`)
		})
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: dbName,
				sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`,
				success(e) {
					console.log(e)
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

//查询数据库数据
export const selectDataList = (tabName,setData,byName,byType) => {
	let setStr = ''
	let sql = ''
	if (JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		dataKeys.forEach((item, index) => {
			console.log(setData[item])
			setStr += (
				`${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`)
		})
		sql = `select * from ${tabName} where ${setStr}`
	} else {
		sql = `select * from ${tabName}`
	}
	if(byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	console.log(sql)
	if (tabName !== undefined) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: dbName,
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

//查询数据库数据 完整sql
export const selectBySQL = (sql) => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: sql,
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
	 
}
 
/**
 * 创建一般索引
 * @param tableName 表名
 * @param indexName 索引名
 * @param fieldName 字段名
 */
export const createIndex = (tableName, indexName, fieldName) => {
	let sql = `CREATE INDEX ${indexName} ON ${tableName} (${fieldName})`
	console.log('新建索引', sql)
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			sql: sql,
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	});
}

/**
 * 判断表索引是否存在
 * @param tableName 表名
 * @param indexName 索引名
 */
export const existsIndex = (tableName, indexName) => {
	let sql = `SELECT count(*)  num FROM sqlite_master WHERE type = 'index' and name = '${indexName}' and tbl_name = '${tableName}'`
	console.log(tableName, indexName, sql)
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: sql,
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log('不存在', e);
				reject(e);
			}
		})
	});
}
 

/**
* 查询所有表字段
* @param tableName 表名
*/
export const existsTabField = (tableName) => {
	let sql = `SELECT sex FROM ${tableName} WHERE type='table';"`
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: sql,
			success(e) {
				console.log(e);
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	});
}
 
/**
 * 判断表字段是否存在
 * @param tableName 表名
 * @param fieldName 字段名
 */
export const existsField = (tableName, fieldName) => {
	let sql = `SELECT count(*)  num FROM sqlite_master WHERE type = 'table' and name = '${fieldName}' and tbl_name = '${tableName}'`
	console.log(tableName, fieldName, sql)
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: sql,
			success(e) {
				console.log('sss',e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	});
}

export const sqlite = {
	isOpen,
	openDb,
	createTab,
	dropTab,
	mergeSql,
	getDataList,
	addTabItem,
	closeSQL,
	deleteInformationType,
	getTable,
	getCount,
	updateSQL,
	isTable,
	selectDataList,
	selectBySQL,
	updateBySQL,
	createIndex, 
	existsIndex,
	existsField,
	existsTabField
};